<?php

namespace zhtfrontend\models\search;

use common\components\helpers\Fun;
use yii\data\ArrayDataProvider;
use yii\db\Query;
use zhtfrontend\models\datas\Admin;
use zhtfrontend\models\datas\CheckCount;
use zhtfrontend\models\datas\Mechanic;
use zhtfrontend\models\datas\MechanicSettlement;
use zhtfrontend\models\datas\MechanicSettlementRelation;
use zhtfrontend\models\datas\OrdersItems;
use zhtfrontend\models\datas\ReportRecord;

/**
 * SettingSearch represents the model behind the search form about `app\models\Setting`.
 */
class OrdersitemsSearch extends OrdersItems
{
    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['id', 'key', 'value', 'name', 'sn', 'created_at'], 'safe'],
        ];
    }


    /**
     * @param $param
     * @return ArrayDataProvider
     * 预约统计
     * 2018-7-9
     */
    public function search_bespeak_mechanic($param)
    {
        $data = (new Query())->select('a.*,b.mechanic_id as mechanic')
            ->from('{{%orders}} a')
            ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
            ->where(['b.teach_type' => 1, 'a.order_type' => 'appointment'])
            ->andWhere(['not in', 'a.status', [2, 3, 4]]);
        if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
            $data = $data->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
        }
        if (isset($param['mechanic_ids']) && !empty($param['mechanic_ids'])) {
            $data = $data->andWhere(['b.mechanic_id' => $param['mechanic_ids']]);
        }
        $data = $data->orderBy('a.id DESC')->groupBy('b.mechanic_id');
        $orders = $data->all(Fun::getSiteDb());
        $items = [];
        foreach ($orders as $k => $v) {
            //机构名称
            $mechanic = (new Query())->select('*')->from('{{%mechanic}}')->where(['id' => $v['mechanic']])->one(Fun::getSiteDb());
            //预约数量
            $all_count = (new Query())->select('b.*')
                ->from('{{%orders}} a')
                ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
                ->where(['b.mechanic_id' => $v['mechanic'], 'a.order_type' => 'appointment', 'b.teach_type' => 1])
                ->andWhere(['not in', 'a.status', [2, 3, 4]]);
            if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
                $all_count = $all_count->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
            }
            $all_count = $all_count->all(Fun::getSiteDb());
            //支付数量
            $pay_count = (new Query())->select('b.*')
                ->from('{{%orders}} a')
                ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
                ->where(['b.mechanic_id' => $v['mechanic'], 'pay_status' => 1, 'a.order_type' => 'appointment', 'b.teach_type' => 1])
                ->andWhere(['not in', 'a.status', [2, 3, 4]]);
            if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
                $pay_count = $pay_count->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
            }
            $pay_count = $pay_count->all(Fun::getSiteDb());
            //班级数量
            $class_count = (new Query())->select('*')->from('{{%class_manage}}')->where(['mechanic_id' => $v['mechanic']])->all(Fun::getSiteDb());
            $items[$k] = [
                'id' => $k + 1,
                'mechanic_id' => $mechanic['id'],
                'mechanic_name' => $mechanic['name'],
                'all_count' => count($all_count),
                'pay_count' => count($pay_count),
                'class_count' => count($class_count),
                'start' => $param['start'],
                'end' => $param['start'],
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'mechanic_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'mechanic_name', 'all_count', 'pay_count', 'class_count'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);

        return $dataProvider;
    }

    /**
     * 预约统计
     * 2018-7-10
     */
    public function search_bespeak_goods($param)
    {
        $data = (new Query())->select('b.*')
            ->from('{{%orders}} a')
            ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
            ->where(['b.teach_type' => 1, 'a.order_type' => 'appointment', 'b.mechanic_id' => $param['mechanic_id']])
            ->andWhere(['not in', 'a.status', [2, 3, 4]]);
        if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
            $data = $data->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
        }
        $data = $data->orderBy('a.id DESC')->groupBy('b.goods_id');
        $orders = $data->all(Fun::getSiteDb());
        $items = [];
        foreach ($orders as $k => $v) {
            //预约人数
            $all_count = (new Query())->select('b.*')
                ->from('{{%orders}} a')
                ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
                ->where(['a.order_type' => 'appointment', 'b.teach_type' => 1, 'b.goods_id' => $v['goods_id']])
                ->andWhere(['not in', 'a.status', [2, 3, 4]]);
            if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
                $all_count = $all_count->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
            }
            $all_count = $all_count->all(Fun::getSiteDb());
            //支付人数
            $pay_count = (new Query())->select('b.*')
                ->from('{{%orders}} a')
                ->leftJoin('{{%orders_items}} b', "b.orders_id = a.id")
                ->where(['pay_status' => 1, 'a.order_type' => 'appointment', 'b.teach_type' => 1, 'b.goods_id' => $v['goods_id']])
                ->andWhere(['not in', 'a.status', [2, 3, 4]]);
            if (isset($param['start']) && !empty($param['start']) && isset($param['end']) && !empty($param['end'])) {
                $pay_count = $pay_count->andWhere(['between', 'a.created_at', $param['start'], $param['end']]);
            }
            $pay_count = $pay_count->all(Fun::getSiteDb());
            //class_manage_id
            $class_manage = (new Query())->select('*')->from('{{%class_manage_goods}}')->where(['goods_id' => $v['goods_id']])->one(Fun::getSiteDb());
            $items[$k] = [
                'id' => $k + 1,
                'goods_id' => $v['goods_id'],
                'goods_name' => $v['name'],
                'all_count' => count($all_count),
                'pay_count' => count($pay_count),
                'class_manage_id' => $class_manage['class_id'],
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'class_manage_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'goods_name', 'all_count', 'pay_count'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * 课销统计
     */
    public function search_check_mechanic($params)
    {
        if (!empty($params['mechanic_id'])) {
            $mechanic_ids = [$params['mechanic_id']];
        } else {
            $mechanic_ids = CheckCount::find()->select('mechanic_id');
            if (isset($params['mechanic_ids']) && !empty($params['mechanic_ids'])) {
                $mechanic_ids = $mechanic_ids->where(['mechanic_id' => $params['mechanic_ids']]);
            }
            if (isset($params['start']) && !empty($params['start'])) {
                $mechanic_ids = $mechanic_ids->andWhere(['between', 'class_time', $params['start'], $params['end']]);
            }
            $mechanic_ids = $mechanic_ids->groupBy('mechanic_id')->column();
        }
        $items = [];
        foreach ($mechanic_ids as $k => $v) {
            $check_count = CheckCount::find()->where(['mechanic_id' => $v]);
            if (isset($params['start']) && !empty($params['start'])) {
                $check_count = $check_count->andWhere(['between', 'class_time', $params['start'], $params['end']]);
            }
            $check_count = $check_count->all();
            $subsidy = 0;
            $proportions = 0;
            $status_count = 0;
            foreach ($check_count as $ck => $cv) {
                $cv->class_type == 'appointment' ? $subsidy += $cv->settlement_price : $proportions += $cv->settlement_price;
                $status_count += $cv->status;
            }
            $mechanic_settlement_relation = MechanicSettlementRelation::findOne(['checkcount_id' => $check_count[0]->id]);
            if (!empty($mechanic_settlement_relation)) {
                $mechanic_settlement = MechanicSettlement::findOne($mechanic_settlement_relation->mechanic_settlement_id);
                $report_record = ReportRecord::find()->where(['module_id' => $mechanic_settlement->id])->orderBy('id DESC')->one();
                $report_record_status = $report_record->status;
                $mechanic_settlement_id = $mechanic_settlement->id;
            } else {
                $report_record_status = 0;
                $mechanic_settlement_id = 0;
            }
            $mechanic = Mechanic::findOne($v);
            $items[$k] = [
                'id' => $k + 1,
                'mechanic_id' => $v,
                'mechanic_name' => $mechanic->name,
                'consume_count' => count($check_count),
                'subsidy' => $subsidy,
                'proportions' => $proportions,
                'status' => $status_count == 0 ? '未结算' : '已结算',
                'time' => date('Y-m', $params['end']),
                'start' => $params['start'],
                'end' => $params['end'],
                'report_record' => $report_record_status,
                's_id' => $mechanic_settlement_id,
                'is_last_month' => time() > $params['end'] ? 1 : 0,
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'mechanic_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'mechanic_name', 'consume_count', 'subsidy', 'proportions', 'status', 'time'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * 课销统计-详情
     * 2018-3-20
     */
    public function search_checkcount($params)
    {
        $check_counts = CheckCount::find()->where(['mechanic_id' => $params['id']]);
        if (isset($params['start']) && !empty($params['start'])) {
            $check_counts = $check_counts->andWhere(['between', 'class_time', $params['start'], $params['end']]);
        }
        $check_counts = $check_counts->all();
        $items = [];
        foreach ($check_counts as $k => $v) {
            if ($v->check_type == 0) {
                $check_user = $v->student->name;
            } else {
                $admin = Admin::findOne($v->check_user);
                $check_user = $admin->name;
            }
            $items[$k] = [
                'id' => $k + 1,
                'teacher_name' => $v->teacher->name,
                'customer_name' => $v->customer->name,
                'student_name' => $v->student->name,
                'goods_name' => isset($v->goods->name) ? $v->goods->name : '--',
                'lesson' => $v->lesson,
                'class_time' => date('Y-m-d H:i:s', $v->class_time),
                'class_type' => $v->class_type,
                'subsidy' => $v->class_type == 'appointment' ? $v->settlement_price : 0,
                'proportions' => $v->class_type == 'consume' ? $v->settlement_price : 0,
                'check_type' => $v->check_type == 0 ? '学生打卡' : '代打卡',
                'check_user' => $check_user,
                'created_at' => date('Y-m-d H:i:s', $v->created_at),
            ];

        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'teacher_name', 'customer_name', 'student_name', 'goods_name', 'lesson', 'class_time', 'class_type', 'subsidy', 'proportions', 'check_type', 'check_user', 'created_at'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $param
     * @return ArrayDataProvider
     * CRM统计-市场负责人
     */
    public function search_crm_market($param)
    {
        $admin_id = (new Query())->select('admin_id')->from('{{%customer_relationship}}')
            ->where(['status' => [0, 1]]);
        if (isset($param['start']) && !empty($param['start'])) {
            $admin_id = $admin_id->andWhere(['between', 'created_at', $param['start'], $param['end']]);
        }
        if (isset($param['admin_ids']) && !empty($param['admin_ids'])) {
            $admin_id = $admin_id->andWhere(['admin_id' => $param['admin_ids']]);
        }
        $admin_id = $admin_id->groupBy('admin_id');
        $admin_id = $admin_id->column(Fun::getSiteDb());
        $items = [];
        foreach ($admin_id as $k => $v) {
            $crm_data = (new Query())->select('*')->from('{{%customer_relationship}}')
                ->where(['status' => [0, 1], 'admin_id' => $v]);
            if (isset($param['start']) && !empty($param['start'])) {
                $crm_data = $crm_data->andWhere(['between', 'created', $param['start'], $param['end']]);
            }
            $crm_data = $crm_data->all(Fun::getSiteDb());
            $potential = 0;
            $intention = 0;
            foreach ($crm_data as $ck => $cv) {
                if ($cv['status'] == 0) {
                    $potential = $potential + 1;
                } else {
                    $intention = $intention + 1;
                }
            }
            $admin = (new Query())->select('*')->from('{{%admin}}')
                ->where(['id' => $v])
                ->one(Fun::getSiteDb());
            $items[$v] = [
                'id' => $k + 1,
                'admin_id' => $v,
                'admin_name' => $admin['name'],
                'potential_count' => $potential,
                'intention_count' => $intention,
                'start' => $param['start'],
                'end' => $param['end'],
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'admin_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'admin_name', 'potential_count', 'intention_count'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $param
     * @return ArrayDataProvider
     */
    public function search_crm_market_default($param)
    {
        $admin_child_ids = (new Query())->select('id,admin_child_ids')
            ->from('{{%customer_relationship}}')
            ->where(['admin_id' => $param['admin_id'], 'status' => [0, 1]]);
        if (isset($param['start']) && !empty($param['start'])) {
            $admin_child_ids = $admin_child_ids->andWhere(['between', 'created_at', $param['start'], $param['end']]);
        }
        $admin_child_ids = $admin_child_ids->all(Fun::getSiteDb());
        $admin_child_id = [];
        foreach ($admin_child_ids as $k => $v) {
            $id_arr = explode(',', $v['admin_child_ids']);
            foreach ($id_arr as $ck => $cv) {
                $admin_child_id[$cv][] = $v['id'];
            }
        }

        $items = [];
        foreach ($admin_child_id as $k => $v) {
            $data_child_arr = (new Query())->select('*')
                ->from('{{%customer_relationship}}')
                ->where(['status' => [0, 1], 'id' => $v]);
            if (isset($param['start']) && !empty($param['start'])) {
                $data_child_arr = $data_child_arr->andWhere(['between', 'created_at', $param['start'], $param['end']]);
            }
            $data_child_arr = $data_child_arr->all(Fun::getSiteDb());
            $admin = (new Query())->select('*')
                ->from('{{%admin}}')
                ->where(['id' => $k])
                ->one(Fun::getSiteDb());
            $potential = 0;
            $intention = 0;
            foreach ($data_child_arr as $dk => $dv) {
                if ($dv['status'] == 0) {
                    $potential = $potential + 1;
                } else {
                    $intention = $intention + 1;
                }
            }
            $items[$k] = [
                'admin_name' => $admin['name'],
                'potential' => $potential,
                'intention' => $intention,
            ];
        }

        $dataProvider = new ArrayDataProvider([
//            'key' => 'admin_id',
            'allModels' => $items,
            'pagination' => ['pagesize' => 10],
            'sort' => [
                'attributes' => ['admin_name', 'potential', 'intention'],
//                'defaultOrder' => ['id' => SORT_ASC],
            ]]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * 转化率统计
     */
    public function search_conversion_mechanic($params)
    {
        $data = self::orders_information($params);
        $items = [];
        foreach ($data as $k => $v) {
            $mechanic = Mechanic::findOne($k);
            $conversion_count_all = array_sum(array_map(create_function('$val', 'return $val["conversion_count"];'), $v));
            $student_count_all = array_sum(array_map(create_function('$val', 'return $val["student_count"];'), $v));
            $student_proportion = $student_count_all == 0 ? 0 : (round($conversion_count_all / $student_count_all, 2) * 100) . '%';
            $items[$k] = [
                'mechanic_id' => $mechanic->id,
                'mechanic_name' => $mechanic->name,
                'conversion_count_all' => $conversion_count_all,
                'student_count_all' => $student_count_all,
                'proportion' => $student_proportion,
                'start' => $params['start'],
                'end' => $params['end'],
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'mechanic_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['mechanic_id', 'mechanic_name', 'conversion_count_all', 'student_count_all', 'proportion'],
                'defaultOrder' => ['mechanic_id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return array
     * 转化率统计
     */
    public function search_conversion_class($params)
    {
        $data = self::orders_information($params);
        $items = [];
        foreach ($data[$params['mechanic_id']] as $k => $v) {
            $student_proportion = $v['student_count'] == 0 ? 0 : (round($v['conversion_count'] / $v['student_count'], 2) * 100) . '%';
            $items[$k] = [
                'goods_id' => $v['goods_id'],
                'goods_name' => $v['goods_name'],
                'conversion_count' => $v['conversion_count'],
                'student_count' => $v['student_count'],
                'proportion' => $student_proportion,
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'goods_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['goods_id', 'goods_name', 'conversion_count', 'student_count', 'proportion'],
                'defaultOrder' => ['goods_id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * 续费率
     */
    public function search_renewal_mechanic($params)
    {
        $data = self::orders_information($params);
        $items = [];
        foreach ($data as $k => $v) {
            $mechanic = Mechanic::findOne($k);
            $renewal_count_all = array_sum(array_map(create_function('$val', 'return $val["renewal_count"];'), $v));
            $student_count_all = array_sum(array_map(create_function('$val', 'return $val["student_count"];'), $v));
            $student_proportion = $student_count_all == 0 ? 0 : (round($renewal_count_all / $student_count_all, 2) * 100) . '%';
            $items[$k] = [
                'mechanic_id' => $mechanic->id,
                'mechanic_name' => $mechanic->name,
                'renewal_count_all' => $renewal_count_all,
                'student_count_all' => $student_count_all,
                'proportion' => $student_proportion,
                'start' => $params['start'],
                'end' => $params['end'],
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'mechanic_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['mechanic_id', 'mechanic_name', 'renewal_count_all', 'student_count_all', 'proportion'],
                'defaultOrder' => ['mechanic_id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     */
    public function search_renewal_class($params)
    {
        $data = self::orders_information($params);
        $items = [];
        foreach ($data[$params['mechanic_id']] as $k => $v) {
            $student_proportion = $v['student_count'] == 0 ? 0 : (round($v['renewal_count'] / $v['student_count'], 2) * 100) . '%';
            $items[$k] = [
                'goods_id' => $v['goods_id'],
                'goods_name' => $v['goods_name'],
                'renewal_count' => $v['renewal_count'],
                'student_count' => $v['student_count'],
                'proportion' => $student_proportion,
            ];
        }
        $dataProvider = new ArrayDataProvider([
            'key' => 'goods_id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['goods_id', 'goods_name', 'renewal_count', 'student_count', 'proportion'],
                'defaultOrder' => ['goods_id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * 续费率、转化率等统计输出
     * renewal-续费、consume-消费、conversion-转化
     */
    public function orders_information($params)
    {
        $information = (new Query())->select('c.class_id,b.mechanic_id,b.name,b.goods_id')
            ->from('{{%orders}} a')
            ->leftJoin('{{%orders_items}} b', 'a.id=b.orders_id')
            ->leftJoin('{{%class_manage_goods}} c', 'b.goods_id = c.goods_id')
            ->where(['a.pay_status' => 1, 'a.order_type' => 'renew', 'b.teach_type' => 1])
            ->andWhere(['not in', 'a.status', [2, 3, 4]]);
        if (isset($params['start']) && !empty($params['start'])) {
            $information = $information->andWhere(['between', 'a.created_at', $params['start'], $params['end']]);
        }
        if (isset($params['mechanic_ids']) && !empty($params['mechanic_ids'])) {
            $information = $information->where(['b.mechanic_id' => $params['mechanic_ids']]);
        }
        $information = $information->groupBy('c.class_id');
        $information = $information->all(Fun::getSiteDb());
        $data = [];
        foreach ($information as $k => $v) {
            $goods_id = (new Query())->select('goods_id')->from('{{%class_manage_goods}}')
                ->where(['class_id' => $v['class_id']])
                ->all(Fun::getSiteDb());
            $conversion_count = 0;
            $consume_count = 0;
            $renewal_count = 0;
            foreach ($goods_id as $gk => $gv) {
                $customer_ids = (new Query())->select('a.customer_id')
                    ->from('{{%orders}} a')
                    ->leftJoin('{{%orders_items}} b', 'a.id=b.orders_id')
                    ->where(['a.pay_status' => 1, 'b.teach_type' => 1, 'b.goods_id' => $gv['goods_id']])
                    ->andWhere(['not in', 'a.status', [2, 3, 4]]);
                if (isset($params['start']) && !empty($params['start'])) {
                    $customer_ids = $customer_ids->andWhere(['between', 'a.created_at', $params['start'], $params['end']]);
                }
                if (isset($params['mechanic_ids']) && !empty($params['mechanic_ids'])) {
                    $customer_ids = $customer_ids->where(['b.mechanic_id' => $params['mechanic_ids']]);
                }
                $customer_ids = $customer_ids->groupBy('a.customer_id');
                $customer_ids = $customer_ids->all(Fun::getSiteDb());

                foreach ($customer_ids as $ck => $cv) {
                    $info = (new Query())->select('a.customer_id,a.order_type,b.*')
                        ->from('{{%orders}} a')
                        ->leftJoin('{{%orders_items}} b', 'a.id=b.orders_id')
                        ->where(['a.pay_status' => 1, 'b.teach_type' => 1, 'b.goods_id' => $gv['goods_id'], 'a.customer_id' => $cv['customer_id']])
                        ->andWhere(['not in', 'a.status', [2, 3, 4]]);
                    if (isset($params['start']) && !empty($params['start'])) {
                        $info = $info->andWhere(['between', 'a.created_at', $params['start'], $params['end']]);
                    }
                    if (isset($params['mechanic_ids']) && !empty($params['mechanic_ids'])) {
                        $info = $info->where(['b.mechanic_id' => $params['mechanic_ids']]);
                    }
                    $info = $info->orderBy('a.created_at asc');
                    $info = $info->all(Fun::getSiteDb());
                    if (count($info) == 1) {
                        if (self::deep_in_array('appointment', $info)) {
                            $conversion_count = $conversion_count + 1;
                        } else {
                            $consume_count = $consume_count + 1;
                        }
                    } else {
                        if (self::deep_in_array('appointment', $info)) {
                            $renewal_count = $renewal_count + count($info) - 2;
                        } else {
                            $renewal_count = $renewal_count + count($info) - 1;
                        }
                    }
                }
            }
            $student_count = (new Query())->select('b.*')
                ->from('{{%orders}} a')
                ->leftJoin('{{%orders_items}} b', 'a.id=b.orders_id')
                ->leftJoin('{{%class_manage_goods}} c', 'b.goods_id = c.goods_id');
            if (isset($params['start']) && !empty($params['start'])) {
                $student_count = $student_count->andWhere(['between', 'a.created_at', $params['start'], $params['end']]);
            }
            if (isset($params['mechanic_ids']) && !empty($params['mechanic_ids'])) {
                $student_count = $student_count->where(['b.mechanic_id' => $params['mechanic_ids']]);
            }
            $student_count = $student_count->where(['a.pay_status' => 1, 'b.teach_type' => 1, 'c.class_id' => $v['class_id']]);
            $student_count = $student_count->groupBy('b.student_id');
            $student_count = $student_count->all(Fun::getSiteDb());
            $data[$v['mechanic_id']][$v['class_id']] = [
                'mechanic_id' => $v['mechanic_id'],
                'goods_id' => $v['goods_id'],
                'goods_name' => $v['name'],
                'conversion_count' => $conversion_count,
                'consume_count' => $consume_count,
                'renewal_count' => $renewal_count,
                'student_count' => count($student_count),
            ];
        }
        return $data;
    }

    /**
     * @param $value
     * @param $array
     * @return bool
     * 判断二维数组中是否有某值
     */
    function deep_in_array($value, $array)
    {
        foreach ($array as $item) {
            if (!is_array($item)) {
                if ($item == $value) {
                    return true;
                } else {
                    continue;
                }
            }
            if (in_array($value, $item)) {
                return true;
            } else if (self::deep_in_array($value, $item)) {
                return true;
            }
        }
        return false;
    }

    /**
     * @param $params
     * @return ArrayDataProvider
     * CRM销售
     */
    public function search_crm_sales($param)
    {
        $crm_sales_data = (new Query())->select('salesman_id')
            ->from('{{%customer_relationship}}');
//            ->where(['status' => [3, 4]])
        if (isset($param['start']) && !empty($param['start'])) {
            $crm_sales_data = $crm_sales_data->andWhere(['between', 'created_at', $param['start'], $param['end']]);
        }
        if (isset($param['admin_ids']) && !empty($param['admin_ids'])) {
            $crm_sales_data = $crm_sales_data->andWhere(['salesman_id' => $param['admin_ids']]);
        }
        $crm_sales_data = $crm_sales_data->groupBy('salesman_id')->column(Fun::getSiteDb());
        $items = [];
        foreach ($crm_sales_data as $k => $v) {
            $admin_data = (new Query())->select('*')
                ->from('{{%customer_relationship}}')
                ->where(['salesman_id' => $v]);
            if (isset($param['start']) && !empty($param['start'])) {
                $admin_data = $admin_data->andWhere(['between', 'created_at', $param['start'], $param['end']]);
            }
            if (isset($param['admin_ids']) && !empty($param['admin_ids'])) {
                $admin_data = $admin_data->andWhere(['salesman_id' => $param['admin_ids']]);
            }
            $admin_data = $admin_data->all(Fun::getSiteDb());
            $market = 0;
            $customer = 0;
            $vip = 0;
            foreach ($admin_data as $ak => $av) {
                switch ($av['status']) {
                    case 0:
                        $market = $market + 1;
                        break;
                    case 1:
                        $market = $market + 1;
                        break;
                    case 2:
                        $market = $market + 1;
                        break;
                    case 3:
                        $customer = $customer + 1;
                        break;
                    case 4:
                        $vip = $vip + 1;
                        break;
                }
            }
            $admin = (new Query())->select('*')
                ->from('{{%admin}}')
                ->where(['id' => $v])
                ->one(Fun::getSiteDb());
            $count = count($admin_data);
            $items[$k] = [
                'id' => $k + 1,
                'sales_name' => $admin['name'],
                'count' => $count,
                'market' => $market,
                'customer' => $customer,
                'vip' => $vip,
                'customer_proportion' => round(($customer / $count), 2),
                'vip_proportion' => round($vip / $count, 2),
            ];
        }

        $dataProvider = new ArrayDataProvider([
            'key' => 'id',
            'allModels' => $items,
            'pagination' => [
                'pagesize' => 10,
            ],
            'sort' => [
                'attributes' => ['id', 'sales_name', 'count', 'market', 'customer', 'vip', 'customer_proportion', 'vip_proportion'],
                'defaultOrder' => ['id' => SORT_ASC],
            ],
        ]);
        return $dataProvider;
    }


}